﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using ReflectionStudio.Core.Events;
using INTEX.DataBase.Tools;
namespace WPFExcelReport
{

    public class JLTVKWBData : BaseFunction
    {
        
        public JLTVKWBData()
            : base()
        {

        }
        public JLTVKWBData(Dictionary<string, object> DicGlobalPara)
            : base(DicGlobalPara)
        {
        }

        public DataSet GetVKWB(DataRow dwABPOS)
        {
            DataSet Result;
            DataSet Return = new DataSet();
            string TableName = "VKWB";
            string ART = dwABPOS["ART"].ToString().Trim();
            string FROMDNDATE = dwABPOS["FROMDNDATE"].ToString().Trim();
            string ENDDNDATE = dwABPOS["ENDDNDATE"].ToString().Trim();
            try
            {
                orlReadCommand = OracleDB.CreateCommand();
                orlReadCommand.CommandText = "SELECT A.VKPART,A.VKPFARBE,B.KDKNAME,A.VKPMENGE1*0.01 AS QTY,A.VKPMEEH, "
                    + " (SELECT REPLACE(REPLACE(REPLACE(trim(T2.TXTKTEXT),'½»»õ·¶Î§',''),':',''),'¡£','')  "
                    + " FROM TXTIND T1,TXTKON T2 WHERE T1.TXTITXTART = 'VKT'  "
                    + " AND T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                    + " AND trim(T1.TXTISIND) = a.vkpaufkrs||a.vkpauflfd||'000LIEF' "
                    + " AND T2.TXTKPOS = '20' AND T1.TXTIMANDANT = A.VKPMANDANT) as delrule, "
                    + " A.VKPLIEFTERM AS QDATE,A.VKPVERZTERM5 AS FDATE, "
                    + " (SELECT SUM(X.WBEWMTR*0.001) FROM WBBEW X "
                    + " WHERE X.WBEWMANDANT = A.VKPMANDANT AND TRIM(X.WBEWART) = TRIM(A.VKPART) "
                    + " AND TRIM(X.WBEWFARBE) = TRIM(A.VKPFARBE) "
                    + " AND X.WBEWBS = '20' AND X.WBEWWAKZ = 'R' AND X.WBEWLB = 'PB' "
                    + " AND X.WBEWAUFTYP = 'V') AS GQTY,A.VKPVERS*0.01 AS SQTY, "
                    + " (SELECT SUM(ROUND(DECODE(A.VKPMEEH,'YRD',X.WBBISTKLG/0.9144,X.WBBISTKLG)*0.001,1)) "
                    + " FROM WBBST X "
                    + " WHERE X.WBBMANDANT = '00' AND X.WBBWAKZ = 'F' AND X.WBBVERSANDKZ <>'J' "
                    + " AND TRIM(X.WBBART) = TRIM(A.VKPART) AND TRIM(X.WBBFARBE) = TRIM(A.VKPFARBE) "
                    + " AND X.WBBZENSUR = '1' AND X.WBBLB = 'CP') AS AQTY, "
                    + " (SELECT SUM(ROUND(DECODE(A.VKPMEEH,'YRD',X.WBBISTKLG/0.9144,X.WBBISTKLG)*0.001,1)) "
                    + " FROM WBBST X "
                    + " WHERE X.WBBMANDANT = '00' AND X.WBBWAKZ = 'F' AND X.WBBVERSANDKZ <>'J' "
                    + " AND TRIM(X.WBBART) = TRIM(A.VKPART) AND TRIM(X.WBBFARBE) = TRIM(A.VKPFARBE) "
                    + " AND X.WBBZENSUR = '2' AND X.WBBLB = 'CP') AS BQTY, "
                    + " (SELECT SUM(ROUND(DECODE(A.VKPMEEH,'YRD',X.WBBISTKLG/0.9144,X.WBBISTKLG)*0.001,1)) "
                    + " FROM WBBST X "
                    + " WHERE X.WBBMANDANT = '00' AND X.WBBWAKZ = 'F' AND X.WBBVERSANDKZ <>'J' "
                    + " AND TRIM(X.WBBART) = TRIM(A.VKPART) AND TRIM(X.WBBFARBE) = TRIM(A.VKPFARBE)  "
                    + " AND X.WBBLB = 'LB') AS LQTY, "
                    + " (SELECT SUM(ROUND(DECODE(A.VKPMEEH,'YRD',X.WBBISTKLG/0.9144,X.WBBISTKLG)*0.001,1)) "
                    + " FROM WBBST X "
                    + " WHERE X.WBBMANDANT = '00' AND X.WBBWAKZ = 'F' AND X.WBBVERSANDKZ <>'J' "
                    + " AND TRIM(X.WBBART) = TRIM(A.VKPART) AND TRIM(X.WBBFARBE) = TRIM(A.VKPFARBE)  "
                    + " AND X.WBBLB = 'DC') AS CQTY, "
                    + " (SELECT SUM(ROUND(DECODE(A.VKPMEEH,'YRD',X.WBBISTKLG/0.9144,X.WBBISTKLG)*0.001,1)) "
                    + " FROM WBBST X "
                    + " WHERE X.WBBMANDANT = '00' AND X.WBBWAKZ = 'F' AND X.WBBVERSANDKZ <>'J' "
                    + " AND TRIM(X.WBBART) = TRIM(A.VKPART) AND TRIM(X.WBBFARBE) = TRIM(A.VKPFARBE)  "
                    + " AND X.WBBLB = 'HX') AS XQTY, "
                    + " (SELECT STRAGG(trim(T2.TXTKTEXT))  "
                    + " FROM TXTIND T1,TXTKON T2 WHERE T1.TXTITXTART = 'VKT'  "
                    + " AND T1.TXTIMANDANT = T2.TXTKMANDANT AND T1.TXTIPIND = T2.TXTKPIND "
                    + " AND trim(T1.TXTISIND) = A.VKPAUFKRS||A.VKPAUFLFD||A.VKPAUFPOS||'BEST' "
                    + " AND T1.TXTIMANDANT = A.VKPMANDANT) AS REMARK "
                    + " FROM VKPOS A,KDSTAMM B "
                    + " WHERE A.VKPMANDANT = '00' "
                    + " AND A.VKPSA<>'AJ' AND A.VKPART LIKE 'J%' "
                    + " AND A.VKPART LIKE '"+ART+"%' "
                    + " AND A.VKPMANDANT = B.KDMANDANT AND A.VKPKUNDNR = B.KDKUNDNR " 
                    + " AND a.vkpliefterm >= '" + FROMDNDATE + "' and a.vkpliefterm <= '" + ENDDNDATE + "' "                    
                    + " ORDER BY VKPART,VKPFARBE "
                    ;
               
                Result = Connect2Proxy(orlReadCommand);
                if (Result.Tables.Count > 0)
                {
                    Result.Tables[0].TableName = TableName;
                    byte[] b;
                    string value;
                    foreach (DataRow dw in Result.Tables[TableName].Rows)
                    {
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["KDKNAME"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["KDKNAME"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["DELRULE"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["DELRULE"] = value;
                        b = Encoding.GetEncoding("ISO-8859-1").GetBytes(dw["REMARK"].ToString().Trim());
                        value = Encoding.GetEncoding("gb2312").GetString(b);
                        dw["REMARK"] = value;
                    }
                }
                Return.Merge(Result);
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return Return;
        }       
        public DataSet BuildJLTVKWBData(DataRow dwUI)
        {
            DataSet setReturn = new DataSet();
            DataSet setResult = new DataSet();
            try
            {
                setResult = GetVKWB(dwUI);
                setReturn.Merge(setResult);                
            }
            catch (Exception ex)
            {
                Tracer.Error(ex.StackTrace, ex);
            }
            return setReturn;
        }
    }
}